﻿using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;


namespace DataAccessLayer
{
    using System.Globalization;

    public class AdoDbContext : IDisposable
    {
        /// <summary>
        /// string for connecting with DB
        /// </summary>
        private string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        /// <summary>
        /// object for connecting with DB
        /// </summary>
        private readonly SqlCeConnection connection;

        /// <summary>
        /// consructor for creating connection
        /// </summary>
        public AdoDbContext()
        {
            connection = new SqlCeConnection(ConnStr);
            try
            {
                connection.Open();
            }
            catch (SqlCeException se)
            {
                System.Web.HttpContext.Current.Response.Write("Connection error: " + se.Message);
            }
        }

        /// <summary>
        /// Implementation of IDisposable, closing connection
        /// </summary>
        public void Dispose()
        {
            connection.Close();
        }

        /// <summary>
        /// Deletes task from DB
        /// </summary>
        /// <param name="id">Task ID</param>
        public void DeleteTaskById(int id)
        {
            string sqlDelete = string.Format("DELETE FROM Tasks WHERE ID = '{0}'", id);
            var delete = new SqlCeCommand(sqlDelete, connection);
            try
            {
                delete.ExecuteNonQuery();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<h2>Deleting task error</h2>\n");
                return;
            }

        }

        /// <summary>
        /// Updates selected task
        /// </summary>
        /// <param name="id">Task ID</param>
        /// <param name="description">Task for updating</param>
        public void UpdateTaskById(int id, Task task)
        {
            string sqlUpdate = string.Format("UPDATE Tasks " +
                "SET Name = '{1}', " +
                "Descrition = '{2}', " +
                "Status = '{3}', " +
                "StartDate = '{4}', " +
                "ProjectName = '{5}', " +

                "WHERE ID = '{0}'",
                id,
                task.Name,
                task.Description,
                task.Status,
                task.DateTask,
                task.ProjectName);
            var update = new SqlCeCommand(sqlUpdate, connection);
            try
            {
                update.ExecuteNonQuery();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<h2>Updating task error</h2>\n");
                return;
            }
        }

        /// <summary>
        /// Adds new task to DB
        /// </summary>
        /// <param name="task">New task</param>
        public void AddTask(Task task)
        {
            string sqlInsert =
                string.Format(
                    "INSERT INTO Tasks (Name, Descrition, Status, StartDate) VALUES ('{0}','{1}','{2}','{3}')",
                    task.Name,
                    task.Description,
                    task.Status,
                    task.DateTask);

            var insert = new SqlCeCommand(sqlInsert, connection);
            try
            {
                insert.ExecuteNonQuery();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<h2>Adding task error</h2>\n");
                return;
            }
        }
        /// <summary>
        /// getting user by login
        /// </summary>
        /// <param name="login">user's login</param>
        /// <returns>user</returns>
        public User GetUserByLogin(string login)
        {
            User user = null;
            string commandStr = string.Format(
                "Select Id, Name, Surname, Password From Users Where Login = '{0}'",
                login);
            var command = new SqlCeCommand(commandStr, connection);
            using (var userReader = command.ExecuteReader())
            {
                // creating user
                while (userReader.Read())
                {
                    int userId = userReader.GetInt32(0);
                    string userName = userReader.GetString(1);
                    string userSurname = userReader.GetString(2);
                    string userPassword = userReader.GetString(3);
                    // getting user role
                    int userRole =
                        (int?)(new SqlCeCommand(
                            "Select RoleType From Roles Where UserID = '" + userId + "'",
                            connection)).ExecuteScalar() ?? 0;
                    user = new User(userId, userSurname, userName, login, userPassword, userRole);
                }
            }
            return user;
        }
        /// <summary>
        /// adding new comment to table Comments
        /// </summary>
        /// <param name="taskId">task's id</param>
        /// <param name="comment">new comment</param>
        public void AddComment(int taskId, Comment comment)
        {
            string commandStr =
                string.Format(
                    "Insert Into Comments(Body, Date, UserID, TaskID) Values('{0}', '{1}', '{2}', '{3}')",
                    comment.Body,
                    comment.PostDateTime.ToString("MM-dd-yyyy hh:mm:ss", CultureInfo.InvariantCulture),
                    comment.Author.Id,
                    taskId);
            var command = new SqlCeCommand(commandStr, connection);
            try
            {
                command.ExecuteNonQuery();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<h2>Adding comment error</h2>\n");
                return;
            }
        }

        /// <summary>
        /// Gets all users from DB
        /// </summary>
        /// <returns>List of Users </returns>
        public IEnumerable<User> GetUsers()
        {
            var users = new List<User>();
            string sqlGet = "SELECT Users.Id, Users.Name, Users.Surname, Users.Login, Users.Password, Roles.RoleType" +
                            " FROM Users, Roles Where Users.ID = Roles.UserID";
            var get = new SqlCeCommand(sqlGet, connection);

            using (var reader = get.ExecuteReader())
            {
                while (reader.Read())
                {
                    users.Add(
                        new User(
                            reader.GetInt32(0),
                            reader.GetString(1),
                            reader.GetString(2),
                            reader.GetString(3),
                            reader.GetString(4),
                            reader.GetInt32(5)));
                }
            }

            return users;
        }

        /// <summary>
        /// Gets specific User from DB
        /// </summary>
        /// <param name="id">User ID</param>
        /// <returns>User</returns>
        public User GetUserById(int id)
        {
            User user = null;
            string commandStr = string.Format("Select DISTINCT Name, Surname, Login, Password From [Users] Where ID = '{0}'", id);
            var command = new SqlCeCommand(commandStr, connection);
            using (var userReader = command.ExecuteReader())
            {
                // creating user
                while (userReader.Read())
                {
                    string userName = userReader.GetString(0);
                    string userSurname = userReader.GetString(1);
                    string userLogin = userReader.GetString(2);
                    string userPassword = userReader.GetString(3);
                    // getting user role
                    int userRole =
                        (int?)(new SqlCeCommand(
                            "Select RoleType From Roles Where UserID = '" + id + "'",
                            connection)).ExecuteScalar() ?? 0;
                    user = new User(id, userSurname, userName, userLogin, userPassword, userRole);
                }
            }
            return user;
        }

        /// <summary>
        /// its not implemented
        /// </summary>
        /// <returns></returns>
        public IEnumerable<Project> GetProjects()
        {
            var projects = new List<Project>();

            //smth to do

            return projects;
        }

        /// <summary>
        /// Method for getting all tasks from database
        /// </summary>
        /// <returns>List of workItems</returns>
        public List<WorkItem> GetTasks()
        {
            var tasks = new List<WorkItem>();
            // getting all task from table Task
            string commandStr =
                string.Format("Select ID, Name, Description, Status, StartDate," +
                                " ProjectName, UserID From Tasks");
            var command = new SqlCeCommand(commandStr, connection);
            using (var taskReader = command.ExecuteReader())
            {
                //creating every task from data reader
                while (taskReader.Read())
                {
                    User user = null;
                    int id = taskReader.GetInt32(0);
                    string name = taskReader.GetString(1);
                    string description = taskReader.GetString(2);
                    int status = taskReader.GetInt32(3);
                    DateTime startDate = taskReader.GetDateTime(4);
                    string projectName = taskReader.GetString(5);
                    int userId = taskReader.GetInt32(6);
                    user = this.GetUserById(userId);
                    // getting all comments for this task
                    commandStr = string.Format("Select Body, Date, UserID From Comments Where TaskID = '{0}'", id);
                    command = new SqlCeCommand(commandStr, connection);

                    var commentList = new List<Comment>();
                    using (var commentReader = command.ExecuteReader())
                    {
                        // creating comment
                        while (commentReader.Read())
                        {
                            string commentBody = commentReader.GetString(0);
                            DateTime commentDate = commentReader.GetDateTime(1);
                            int commentUserId = commentReader.GetInt32(2);
                            // getting author of this comment
                            commandStr =
                                string.Format("Select Name, Surname, Login, Password From [Users] Where ID = '{0}'",
                                    commentUserId);
                            command = new SqlCeCommand(commandStr, connection);
                            User author = null;
                            using (var userReader = command.ExecuteReader())
                            {
                                // creating author
                                while (userReader.Read())
                                {
                                    string commentAuthorName = userReader.GetString(0);
                                    string commentAuthorSurname = userReader.GetString(1);
                                    string commentAuthorLogin = userReader.GetString(2);
                                    string commentAuthorPassword = userReader.GetString(3);
                                    int commentUserRole = (int?)
                                        (new SqlCeCommand(
                                            "Select RoleType From Roles Where UserID = '" + commentUserId + "'",
                                            connection)).ExecuteScalar() ?? 0;
                                    author = new User(
                                        commentUserId,
                                        commentAuthorSurname,
                                        commentAuthorName,
                                        commentAuthorLogin,
                                        commentAuthorPassword,
                                        commentUserRole);
                                }
                            }
                            commentList.Add(new Comment(author, commentBody, commentDate));
                        }
                    }
                    tasks.Add(
                        new Task(
                            id,
                            name,
                            description,
                            new object[] { },
                            user,
                            (Statuses)status,
                            projectName,
                            startDate,
                            commentList));
                }
            }
            return tasks;
        }

        public bool IsUserValid(string login, string password)
        {
                string sqlSelectCommand = "SELECT Password" +
                            " FROM [Users] Where [Users].Login = @userlogin";
                var command = new SqlCeCommand(sqlSelectCommand, connection);
                command.Parameters.AddWithValue("@userlogin", login);
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        return SimpleHash.VerifyHash(password, reader.GetString(0));
                    }
                }
                return false;
        }
    }
}
